﻿using System;
using System.Configuration;
using System.Data;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using Books.DAL.ModelProxies;
using Books.Model;

namespace Books.DAL
{
    internal class BooksContext : ObjectContext
    {
        public static readonly string ConnectionName = "BooksConnection";

        public BooksContext(EntityConnection connection)
        : base(connection)
        {
        }

        public int CreatePublisher(PublisherModel model)
        {
            return ExecuteStoreQuery<int>("CreatePublisher @publisherName, @city", new SqlParameter[]
                {
                    new SqlParameter() { ParameterName = "publisherName", SqlDbType = SqlDbType.NVarChar, Size = 100, Value = model.PublisherName },
                    new SqlParameter() { ParameterName = "city", SqlDbType = SqlDbType.NVarChar, Size = 50, Value = model.City }
                }).SingleOrDefault();
        }

        public void DeletePublisher(int publisherId)
        {
            ExecuteStoreCommand("DeletePublisher @publisherId",
                 new SqlParameter() { ParameterName = "publisherId", SqlDbType = SqlDbType.Int, Value = publisherId });
        }

        public AuthorModel GetAuthorById(int authorId)
        {
            return ExecuteStoreQuery<AuthorProxy>("GetAuthorById @authorId",
                new SqlParameter() { ParameterName = "authorId", SqlDbType = SqlDbType.Int, Value = authorId }).FirstOrDefault();
        }

        public AuthorModel[] GetAuthors()
        {
            return ExecuteStoreQuery<AuthorProxy>("GetAuthors").ToArray();
        }

        public AuthorModel[] GetAuthorsForBook(int bookId)
        {
            return ExecuteStoreQuery<AuthorProxy>("GetAuthorsForBook @bookId",
                new SqlParameter() { ParameterName = "bookId", SqlDbType = SqlDbType.Int, Value = bookId }).ToArray();
        }

        public AuthorModel[] GetAuthorsForPublisher(int publisherId)
        {
            return ExecuteStoreQuery<AuthorProxy>("GetAuthorsForPublisher @publisherId",
                new SqlParameter() { ParameterName = "publisherId", SqlDbType = SqlDbType.Int, Value = publisherId }).ToArray();
        }

        public BookModel GetBookById(int bookId)
        {
            return ExecuteStoreQuery<BookProxy>("GetBookById @bookId",
                new SqlParameter() { ParameterName = "bookId", SqlDbType = SqlDbType.Int, Value = bookId }).FirstOrDefault();
        }

        public BookModel[] GetBooks()
        {
            return ExecuteStoreQuery<BookProxy>("GetBooks").ToArray();
        }

        public BookModel[] GetBooksForAuthor(int authorId)
        {
            return ExecuteStoreQuery<BookProxy>("GetBooksForAuthor @authorId",
                new SqlParameter() { ParameterName = "authorId", SqlDbType = SqlDbType.Int, Value = authorId }).ToArray();
        }

        public BookModel[] GetBooksForPublisher(int publisherId)
        {
            return ExecuteStoreQuery<BookProxy>("GetBooksForPublisher @publisherId",
                new SqlParameter() { ParameterName = "publisherId", SqlDbType = SqlDbType.Int, Value = publisherId }).ToArray();
        }

        public PublisherModel GetPublisherById(int publisherId)
        {
            return ExecuteStoreQuery<PublisherProxy>("GetPublisherById @publisherId",
                new SqlParameter() { ParameterName = "publisherId", SqlDbType = SqlDbType.Int, Value = publisherId }).FirstOrDefault();
        }

        public PublisherModel[] GetPublishers(string orderBy)
        {
            return ExecuteStoreQuery<PublisherProxy>("GetPublishers '@orderBy'",
                new SqlParameter() { ParameterName = "orderBy", SqlDbType = SqlDbType.VarChar, Size = 100,
                    Value = String.IsNullOrEmpty(orderBy) ? DBNull.Value : (object) orderBy }).ToArray();
        }

        public PublisherModel[] GetPublishersForAuthor(int authorId)
        {
            return ExecuteStoreQuery<PublisherProxy>("GetPublishersForAuthor @authorId",
                new SqlParameter() { ParameterName = "authorId", SqlDbType = SqlDbType.Int, Value = authorId }).ToArray();
        }

        public PublisherModel[] MatchPublishers(string publisherName, string city)
        {
            return ExecuteStoreQuery<PublisherProxy>("MatchPublishers @publisherName, @city", new SqlParameter[]
                {
                    new SqlParameter() {ParameterName = "publisherName", SqlDbType = SqlDbType.NVarChar, Size = 100, Value = String.IsNullOrEmpty(publisherName) ? DBNull.Value : (object) publisherName },
                    new SqlParameter() {ParameterName = "city", SqlDbType = SqlDbType.NVarChar, Size = 50, Value = String.IsNullOrEmpty(city) ? DBNull.Value : (object) city },
                }).ToArray();
        }

        public void UpdatePublisher(PublisherModel model)
        {
            ExecuteStoreCommand("UpdatePublisher @publisherId, @publisherName, @city", new SqlParameter[]
                {
                    new SqlParameter() { ParameterName = "publisherId", SqlDbType = SqlDbType.Int, Value = model.PublisherId },
                    new SqlParameter() { ParameterName = "publisherName", SqlDbType = SqlDbType.NVarChar, Size = 100, Value = model.PublisherName },
                    new SqlParameter() { ParameterName = "city", SqlDbType = SqlDbType.NVarChar, Size = 50, Value = model.City },
                });
        }
    }
}
